/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DAO;

import DTO.ContactDTO;
import UTIL.ConvertString;
import UTIL.SqlDataAccessHelper;
import java.io.UnsupportedEncodingException;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.swing.JOptionPane;

/**
 *
 * @author trquanglong
 */
public class ContactDAO {

    

    /**
     * Get all contacts
     * @return
     * @throws java.sql.SQLException
     * @throws java.io.UnsupportedEncodingException
     */
    public static ArrayList<ContactDTO> getListContact() throws SQLException, UnsupportedEncodingException{
        Connection connect = null;
        ArrayList<ContactDTO> lstContact = new ArrayList<ContactDTO>();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select * from Contacts";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                ContactDTO contact = new ContactDTO();
                contact.setIdContact(rs.getInt("ID"));
                contact.setName(ConvertString.ConvertUTF82String(rs.getString("Name")));
                contact.setScreenName(ConvertString.ConvertUTF82String(rs.getString("ScreenName")));
                contact.setEmail(ConvertString.ConvertUTF82String(rs.getString("Email")));
                contact.setAdditionalEmail(ConvertString.ConvertUTF82String(rs.getString("AdditionalEmail")));
                contact.setOrganization(ConvertString.ConvertUTF82String(rs.getString("Organization")));
                contact.setMobile(ConvertString.ConvertUTF82String(rs.getString("Mobile")));
                contact.setHomePhone(ConvertString.ConvertUTF82String(rs.getString("HomePhone")));
                contact.setWorkPhone(ConvertString.ConvertUTF82String(rs.getString("WorkPhone")));
                contact.setFax(ConvertString.ConvertUTF82String(rs.getString("Fax")));
                contact.setHomeAddress(ConvertString.ConvertUTF82String(rs.getString("HomeAddress")));
                contact.setBusinessAddress(ConvertString.ConvertUTF82String(rs.getString("BusinessAddress")));
                contact.setCountry(ConvertString.ConvertUTF82String(rs.getString("Country")));
                contact.setIdGroup(rs.getInt("IDGroup"));

                lstContact.add(contact);
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstContact;
    }

    /**
     * Get List Contact with string SQL
     * @param strSQL
     * @return
     */
    public static ArrayList<ContactDTO> getListContact(String strSQL) throws UnsupportedEncodingException, SQLException {
        Connection connect = null;
        ArrayList<ContactDTO> lstContact = new ArrayList<ContactDTO>();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            //String sqlQuery = "Select * from Contacts Where Organization = '" + ConvertString.ConvertString2UTF8(company) + "'";
            ResultSet rs = stmt.executeQuery(strSQL);
            while(rs.next()){
                ContactDTO contact = new ContactDTO();
                contact.setIdContact(rs.getInt("ID"));
                contact.setName(ConvertString.ConvertUTF82String(rs.getString("Name")));
                contact.setScreenName(ConvertString.ConvertUTF82String(rs.getString("ScreenName")));
                contact.setEmail(ConvertString.ConvertUTF82String(rs.getString("Email")));
                contact.setAdditionalEmail(ConvertString.ConvertUTF82String(rs.getString("AdditionalEmail")));
                contact.setOrganization(ConvertString.ConvertUTF82String(rs.getString("Organization")));
                contact.setMobile(ConvertString.ConvertUTF82String(rs.getString("Mobile")));
                contact.setHomePhone(ConvertString.ConvertUTF82String(rs.getString("HomePhone")));
                contact.setWorkPhone(ConvertString.ConvertUTF82String(rs.getString("WorkPhone")));
                contact.setFax(ConvertString.ConvertUTF82String(rs.getString("Fax")));
                contact.setHomeAddress(ConvertString.ConvertUTF82String(rs.getString("HomeAddress")));
                contact.setBusinessAddress(ConvertString.ConvertUTF82String(rs.getString("BusinessAddress")));
                contact.setCountry(ConvertString.ConvertUTF82String(rs.getString("Country")));
                contact.setIdGroup(rs.getInt("IDGroup"));
                lstContact.add(contact);
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstContact;
    }
/**
 * get all of list Contact by idGroup.
 * @param idGroup
 * @return
 * @throws java.sql.SQLException
 * @throws java.io.UnsupportedEncodingException
 */
    public static ArrayList<ContactDTO> getListContactOfGroup(int idGroup) throws SQLException, UnsupportedEncodingException{
        Connection connect = null;
        ArrayList<ContactDTO> lstContact = new ArrayList<ContactDTO>();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select * from Contacts Where IDGroup = " + Integer.toString(idGroup);
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                ContactDTO contact = new ContactDTO();
                contact.setIdContact(rs.getInt("ID"));
                contact.setName(ConvertString.ConvertUTF82String(rs.getString("Name")));
                contact.setScreenName(ConvertString.ConvertUTF82String(rs.getString("ScreenName")));
                contact.setEmail(ConvertString.ConvertUTF82String(rs.getString("Email")));
                contact.setAdditionalEmail(ConvertString.ConvertUTF82String(rs.getString("AdditionalEmail")));
                contact.setOrganization(ConvertString.ConvertUTF82String(rs.getString("Organization")));
                contact.setMobile(ConvertString.ConvertUTF82String(rs.getString("Mobile")));
                contact.setHomePhone(ConvertString.ConvertUTF82String(rs.getString("HomePhone")));
                contact.setWorkPhone(ConvertString.ConvertUTF82String(rs.getString("WorkPhone")));
                contact.setFax(ConvertString.ConvertUTF82String(rs.getString("Fax")));
                contact.setHomeAddress(ConvertString.ConvertUTF82String(rs.getString("HomeAddress")));
                contact.setBusinessAddress(ConvertString.ConvertUTF82String(rs.getString("BusinessAddress")));
                contact.setCountry(ConvertString.ConvertUTF82String(rs.getString("Country")));
                contact.setIdGroup(rs.getInt("IDGroup"));
                lstContact.add(contact);
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstContact;
    }
/**
 * get list contact by company.
 * @param company
 * @return
 * @throws java.sql.SQLException
 * @throws java.io.UnsupportedEncodingException
 */
    public static ArrayList<ContactDTO> getListContactOfCompany(String company) throws SQLException, UnsupportedEncodingException{
        Connection connect = null;
        ArrayList<ContactDTO> lstContact = new ArrayList<ContactDTO>();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select * from Contacts Where Organization = '" + ConvertString.ConvertString2UTF8(company) + "'";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                ContactDTO contact = new ContactDTO();
                contact.setIdContact(rs.getInt("ID"));
                contact.setName(ConvertString.ConvertUTF82String(rs.getString("Name")));
                contact.setScreenName(ConvertString.ConvertUTF82String(rs.getString("ScreenName")));
                contact.setEmail(ConvertString.ConvertUTF82String(rs.getString("Email")));
                contact.setAdditionalEmail(ConvertString.ConvertUTF82String(rs.getString("AdditionalEmail")));
                contact.setOrganization(ConvertString.ConvertUTF82String(rs.getString("Organization")));
                contact.setMobile(ConvertString.ConvertUTF82String(rs.getString("Mobile")));
                contact.setHomePhone(ConvertString.ConvertUTF82String(rs.getString("HomePhone")));
                contact.setWorkPhone(ConvertString.ConvertUTF82String(rs.getString("WorkPhone")));
                contact.setFax(ConvertString.ConvertUTF82String(rs.getString("Fax")));
                contact.setHomeAddress(ConvertString.ConvertUTF82String(rs.getString("HomeAddress")));
                contact.setBusinessAddress(ConvertString.ConvertUTF82String(rs.getString("BusinessAddress")));
                contact.setCountry(ConvertString.ConvertUTF82String(rs.getString("Country")));
                contact.setIdGroup(rs.getInt("IDGroup"));
                lstContact.add(contact);
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstContact;
    }
/**
 * get list contact by country.
 * @param country
 * @return
 * @throws java.sql.SQLException
 * @throws java.io.UnsupportedEncodingException
 */
     public static ArrayList<ContactDTO> getListContactOfLocation(String country) throws SQLException, UnsupportedEncodingException{
        Connection connect = null;
        ArrayList<ContactDTO> lstContact = new ArrayList<ContactDTO>();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select * from Contacts Where Country = '" + ConvertString.ConvertString2UTF8(country) + "'";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                ContactDTO contact = new ContactDTO();
                contact.setIdContact(rs.getInt("ID"));
                contact.setName(ConvertString.ConvertUTF82String(rs.getString("Name")));
                contact.setScreenName(ConvertString.ConvertUTF82String(rs.getString("ScreenName")));
                contact.setEmail(ConvertString.ConvertUTF82String(rs.getString("Email")));
                contact.setAdditionalEmail(ConvertString.ConvertUTF82String(rs.getString("AdditionalEmail")));
                contact.setOrganization(ConvertString.ConvertUTF82String(rs.getString("Organization")));
                contact.setMobile(ConvertString.ConvertUTF82String(rs.getString("Mobile")));
                contact.setHomePhone(ConvertString.ConvertUTF82String(rs.getString("HomePhone")));
                contact.setWorkPhone(ConvertString.ConvertUTF82String(rs.getString("WorkPhone")));
                contact.setFax(ConvertString.ConvertUTF82String(rs.getString("Fax")));
                contact.setHomeAddress(ConvertString.ConvertUTF82String(rs.getString("HomeAddress")));
                contact.setBusinessAddress(ConvertString.ConvertUTF82String(rs.getString("BusinessAddress")));
                contact.setCountry(ConvertString.ConvertUTF82String(rs.getString("Country")));
                contact.setIdGroup(rs.getInt("IDGroup"));
                lstContact.add(contact);
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstContact;
    }
/**
 * add new contact to databse
 * @param contact
 * @throws java.io.UnsupportedEncodingException
 * @throws java.sql.SQLException
 */
    public static void addContactToDatabase (ContactDTO contact) throws UnsupportedEncodingException, SQLException{
       

        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();

            String sqlQuery = "Insert into Contacts (Name, ScreenName, Email, AdditionalEmail, Organization, Mobile, HomePhone, WorkPhone, Fax, HomeAddress, BusinessAddress, Country, IDGroup)" +
                    " Values ('" + ConvertString.ConvertString2UTF8(contact.getName()) + "', '" + ConvertString.ConvertString2UTF8(contact.getScreenName()) + "', '" +
                    ConvertString.ConvertString2UTF8(contact.getEmail()) + "', '" + ConvertString.ConvertString2UTF8(contact.getAdditionalEmail()) + "', '" +
                    ConvertString.ConvertString2UTF8(contact.getOrganization()) + "', '" + ConvertString.ConvertString2UTF8(contact.getMobile()) + "', '" +
                    ConvertString.ConvertString2UTF8(contact.getHomePhone()) + "', '" + ConvertString.ConvertString2UTF8(contact.getWorkPhone()) + "', '" +
                    ConvertString.ConvertString2UTF8(contact.getFax()) + "', '" + ConvertString.ConvertString2UTF8(contact.getHomeAddress()) + "', '" +
                    ConvertString.ConvertString2UTF8(contact.getBusinessAddress()) + "', '" + ConvertString.ConvertString2UTF8(contact.getCountry()) + "', " +
                    Integer.toString(contact.getIdGroup()) + ")";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }
/**
 * get all company.
 * @return
 * @throws java.io.UnsupportedEncodingException
 * @throws java.sql.SQLException
 */
    public static ArrayList<String> getListCompany() throws UnsupportedEncodingException, SQLException{
        ArrayList <String> lstCompany = new ArrayList<String>();
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select distinct Organization from Contacts ";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                lstCompany.add(ConvertString.ConvertUTF82String(rs.getString("Organization")));
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstCompany;

    }
/**
 * get all location.
 * @return
 * @throws java.io.UnsupportedEncodingException
 * @throws java.sql.SQLException
 */
    public static ArrayList<String> getListLocation() throws UnsupportedEncodingException, SQLException{
        ArrayList <String> lstCountry = new ArrayList<String>();
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select distinct Country from Contacts ";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                lstCountry.add(ConvertString.ConvertUTF82String(rs.getString("Country")));
            }
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return lstCountry;
    }
/**
 * get all data.
 * @return
 */
    public static ArrayList<String> getListData(){
        ArrayList <String> lstData = new ArrayList<String>();
        return lstData;
    }
/**
 * dete contact by name.
 * @param nameContact
 * @throws java.sql.SQLException
 * @throws java.io.UnsupportedEncodingException
 */
    public static void deleteContact (String nameContact) throws SQLException, UnsupportedEncodingException{
       Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Contacts Where Name = '" + ConvertString.ConvertString2UTF8(nameContact) + "'";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }
/**
 * get contact by name.
 * @param name
 * @return
 * @throws java.io.UnsupportedEncodingException
 * @throws java.sql.SQLException
 */
    public static ContactDTO getContact (String name) throws UnsupportedEncodingException, SQLException{
        Connection connect = null;
        ContactDTO contact = new ContactDTO();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Select * from Contacts Where Name = '" + ConvertString.ConvertString2UTF8(name) + "'";
            ResultSet rs = stmt.executeQuery(sqlQuery);
            while(rs.next()){
                contact.setIdContact(rs.getInt("ID"));
                contact.setName(ConvertString.ConvertUTF82String(rs.getString("Name")));
                contact.setScreenName(ConvertString.ConvertUTF82String(rs.getString("ScreenName")));
                contact.setEmail(ConvertString.ConvertUTF82String(rs.getString("Email")));
                contact.setAdditionalEmail(ConvertString.ConvertUTF82String(rs.getString("AdditionalEmail")));
                contact.setOrganization(ConvertString.ConvertUTF82String(rs.getString("Organization")));
                contact.setMobile(ConvertString.ConvertUTF82String(rs.getString("Mobile")));
                contact.setHomePhone(ConvertString.ConvertUTF82String(rs.getString("HomePhone")));
                contact.setWorkPhone(ConvertString.ConvertUTF82String(rs.getString("WorkPhone")));
                contact.setFax(ConvertString.ConvertUTF82String(rs.getString("Fax")));
                contact.setHomeAddress(ConvertString.ConvertUTF82String(rs.getString("HomeAddress")));
                contact.setBusinessAddress(ConvertString.ConvertUTF82String(rs.getString("BusinessAddress")));
                contact.setCountry(ConvertString.ConvertUTF82String(rs.getString("Country")));
                contact.setIdGroup(rs.getInt("IDGroup"));
            }

            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
        return contact;
    }

    /**
     * Delete All contacts in database
     * @throws java.sql.SQLException
     */
    public static void deleteAllContacts() throws SQLException{
         Connection connect = null;
        ContactDTO contact = new ContactDTO();
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Contacts";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }


    /**
     * Delete all contacts of one group
     * @param nameGroup
     */
    public static void deleteContactsOfGroup(String nameGroup) throws SQLException, UnsupportedEncodingException {
        Connection connect = null;
        int idGroup = ContactGroupDAO.getIDGroup(nameGroup);
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Contacts Where IDGroup = " + Integer.toString(idGroup);
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }

    /**
     * Delete All Contacts of Group Location
     * @param location
     * @throws java.io.UnsupportedEncodingException
     * @throws java.sql.SQLException
     */
    public static void delteteLocation(String location) throws UnsupportedEncodingException, SQLException{
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Contacts Where Country = '" + ConvertString.ConvertString2UTF8(location) + "'";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }

    /**
     * Delete all contacts of company
     * @param company
     * @throws java.io.UnsupportedEncodingException
     * @throws java.sql.SQLException
     */
    public static void deleteCompany(String company) throws UnsupportedEncodingException, SQLException{
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Contacts Where Organization = '" + ConvertString.ConvertString2UTF8(company) + "'";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }

    /**
     * Delete all contacts have data file outlook
     * @param dataFile
     * @throws java.sql.SQLException
     */
    public static void deleteDataFile(String dataFile) throws SQLException{
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Delete * from Contacts";
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }

    /**
     * Update information for one contact
     * @param newContact
     * @throws java.sql.SQLException
     */
    public static void updateContact (ContactDTO newContact) throws SQLException, UnsupportedEncodingException{
        Connection connect = null;
        try
        {
            connect = SqlDataAccessHelper.getConnection();
            Statement stmt = (Statement) connect.createStatement();
            String sqlQuery = "Update Contacts Set Name = '" + ConvertString.ConvertString2UTF8(newContact.getName()) + "', " +
                    "ScreenName = '" + ConvertString.ConvertString2UTF8(newContact.getScreenName()) + "', " +
                    "Email = '" + ConvertString.ConvertString2UTF8(newContact.getEmail()) + "', " +
                    "AdditionalEmail = '" + ConvertString.ConvertString2UTF8(newContact.getAdditionalEmail()) + "', " +
                    "Organization = '" + ConvertString.ConvertString2UTF8(newContact.getOrganization()) + "', " +
                    "Mobile = '" + ConvertString.ConvertString2UTF8(newContact.getMobile()) + "', " +
                    "HomePhone = '" + ConvertString.ConvertString2UTF8(newContact.getHomePhone()) + "', " +
                    "WorkPhone = '" +   ConvertString.ConvertString2UTF8(newContact.getWorkPhone())  + "'," +
                    "Fax = '" +  ConvertString.ConvertString2UTF8(newContact.getFax()) + "', " +
                    "HomeAddress = '" +  ConvertString.ConvertString2UTF8(newContact.getHomeAddress()) + "', " +
                    "BusinessAddress = '" +  ConvertString.ConvertString2UTF8(newContact.getBusinessAddress()) + "', " +
                    "Country = '" + ConvertString.ConvertString2UTF8(newContact.getCountry()) + "', " +
                    "IDGroup = " + Integer.toString(newContact.getIdGroup()) + " Where ID = " + Integer.toString(newContact.getIdContact());
            stmt.executeQuery(sqlQuery);
            stmt.close();
        }catch (SQLException ex) {
        }
        connect.close();
    }







}
